package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.BookGrantInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.request.BookGrantInfoRs;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 发放记录表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-02-10
 */
public interface BookGrantInfoMapper extends BaseMapper<BookGrantInfo> {

    /**
     * 发放记录
     * @param yearPart
     * @param quarterNum
     * @param grantId
     * @param status
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "t1.*,",
                    "t3.book_text_id,",
                    "t3.student_id,",
                    "t3.subjects_id,",
                    "t3.lesson_num,",
                    "t2.NAME AS teacher_name, ",
                    "t4.`name` AS student_name",
                    "FROM",
                    "book_grant_info AS t1",
                    "INNER JOIN book_student AS t3 ON t1.book_student_id = t3.id and t3.deleted = 0",
                    "LEFT JOIN staff AS t2 ON t1.grant_id = t2.id ",
                    "LEFT JOIN student AS t4 ON t1.grant_id = t4.id ",
                    "where",
                    "t1.year_part = ${yearPart}",
                    "and t1.quarter_num = ${quarterNum}",
                    "<when test = 'grantId!=null'>",
                        "and t1.grant_id = ${grantId}",
                    "</when>",
                    "and t1.status = ${status}",
                    "AND t3.school_id = ${schoolId}",
                    "<when test = 'subjectsId!=null'>",
                        "AND t3.subjects_id = ${subjectsId}",
                    "</when>",
                    "GROUP BY",
                    "t1.grant_id,",
                    //"t3.book_text_id,",
                    //"t3.subjects_id,",
                    "t1.created_at",
                    "ORDER BY",
                    "t1.created_at DESC",
                    "<when test = 'limitCount!=null'>",
                        "limit ${limitCount}",
                    "</when>",
            "</script>"})
    public List<BookGrantInfo> getGrantLog(@Param("yearPart")Integer yearPart,
                                           @Param("quarterNum")Integer quarterNum,
                                           @Param("schoolId")Long schoolId,
                                           @Param("subjectsId")Long subjectsId,
                                           @Param("grantId")Long grantId,
                                           @Param("status") Integer status,
                                           @Param("limitCount") Integer limitCount
                                           );



    /**
     * 发放记录详情
     * @param yearPart
     * @param quarterNum
     * @param teacherId
     * @param status
     * @param createdAt
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.*,",
            "t3.book_text_id,",
            "t3.book_text_type,",
            "t3.lesson_num,",
            "t3.year_class_id,",
            "t3.school_id,",
            "t3.subjects_id,",
            "t3.class_type_id,",
            "getDictName(t3.year_class_id, 14) as year_class_name,",
            "getSchoolName(t3.school_id) as school_name,",
            "getSubjectsName(t3.subjects_id) as subjects_name,",
            "getCourseTypeName(t3.class_type_id) as class_type_name,",
            "t2.`name` AS teacher_name,",
            "t4.`name` AS book_text_name,",
            "t5.`name` AS student_name,",
            "t6.`name` AS grade_name,",
            "count(t1.id) as count",
            "FROM",
            "book_grant_info AS t1",
            "INNER JOIN book_student AS t3 ON t1.book_student_id = t3.id and t3.deleted = 0",
            "LEFT JOIN book_text AS t4 ON t3.book_text_id = t4.id",
            "INNER JOIN staff AS t2 ON t1.grant_id = t2.id ",
            "LEFT JOIN student AS t5 ON t5.id = t3.student_id ",
            "INNER JOIN grade AS t6 ON t3.grade_id = t6.id AND t6.deleted = 0",
            "where",
            "t1.year_part = ${yearPart}",
            "and t1.quarter_num = ${quarterNum}",
            "and t1.grant_id = ${teacherId}",
            "and t1.status = ${status}",
            "and t1.created_at = #{createdAt}",
            "AND t3.school_id = ${schoolId}",
            //"AND t3.subjects_id = ${subjectsId}",
            "<when test = 'bookTextId!=null'>",
                "AND t3.book_text_id = ${bookTextId}",
            "</when>",
            "<when test = 'bookTextType!=null'>",
                "AND t3.book_text_type = ${bookTextType}",
            "</when>",
            "<when test='gradeId !=null and gradeId != &apos;&apos;'>",
                "AND t3.grade_id IN ( ${gradeId} )",
            "</when>",
            "<when test='studentName !=null and studentName != &apos;&apos;'>",
                "AND t5.`name` LIKE '%${studentName}%' ",
            "</when>",
            "<when test='bookTextId !=null and bookTextId != &apos;&apos;'>",
                "AND t3.`book_text_id` = ${bookTextId} ",
            "</when>",
            "<when test='classTypeId !=null and classTypeId != &apos;&apos;'>",
                "AND t3.`class_type_id` = ${classTypeId} ",
            "</when>",
            "<when test='yearClassId !=null and yearClassId != &apos;&apos;'>",
                "AND t3.`year_class_id` = ${yearClassId} ",
            "</when>",
            "<if test='isDetails == 1'>",
                "AND t3.lesson_num = ${lessonNum}",
            "</if>",
            "GROUP BY",
            "t1.grant_id,",
            "t1.created_at,",
            "t3.subjects_id,",
            "t3.year_class_id,",
            "t3.class_type_id,",
            "t3.book_text_id,",
            "t3.book_text_type,",
            "t3.lesson_num",
            "<if test='isDetails == 1'>",
                ",t3.id ",
            "</if>",
            "ORDER BY",
            "t1.created_at DESC",
            "</script>"})
    public List<BookGrantInfoRs> getGrantLogDetail(@Param("yearPart") Integer yearPart,
                                                   @Param("quarterNum") Integer quarterNum,
                                                   @Param("schoolId") Long schoolId,
                                                   @Param("subjectsId") Long subjectsId,
                                                   @Param("teacherId") Long teacherId,
                                                   @Param("status") Integer status,
                                                   @Param("bookTextId") Long bookTextId,
                                                   @Param("createdAt") String createdAt,
                                                   @Param("bookTextType") Integer bookTextType,
                                                   @Param("classTypeId") Long classTypeId,
                                                   @Param("yearClassId") String yearClassId,
                                                   @Param("lessonNum") Integer lessonNum,
                                                   @Param("studentName") String studentName,
                                                   @Param("gradeId") String gradeId,
                                                   @Param("isDetails") Integer isDetails
    );

    /**
     * 发放记录详情
     * @param yearPart
     * @param quarterNum
     * @param teacherId
     * @param status
     * @param createdAt
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.*,",
            "getDictName(t3.year_class_id, 14) as year_class_name,",
            "getSchoolName(t3.school_id) as school_name,",
            "getSubjectsName(t3.subjects_id) as subjects_name,",
            "getCourseTypeName(t3.class_type_id) as class_type_name,",
            "t2.NAME AS teacher_name,",
            "count(t1.id) as count",
            "FROM",
            "book_grant_info_paper AS t1",
            "INNER JOIN book_student_paper AS t3 ON t1.book_student_id = t3.id and t3.deleted = 0",
            "INNER JOIN staff AS t2 ON t1.grant_id = t2.id ",
            "where",
            "t1.year_part = ${yearPart}",
            "and t1.quarter_num = ${quarterNum}",
            "and t1.grant_id = ${teacherId}",
            "and t1.status = ${status}",
            "and t1.created_at = #{createdAt}",
            "AND t3.school_id = ${schoolId}",
            "AND t3.subjects_id = ${subjectsId}",
            "GROUP BY",
            "t1.grant_id,",
            "t1.created_at,",
            "t3.subjects_id,",
            "t3.year_class_id,",
            "t3.class_type_id",
            "ORDER BY",
            "t1.created_at DESC",
            "</script>"})
    public List<BookGrantInfoRs> getGrantLogDetailPaper(@Param("yearPart")Integer yearPart,
                                                   @Param("quarterNum")Integer quarterNum,
                                                   @Param("schoolId")Long schoolId,
                                                   @Param("subjectsId")Long subjectsId,
                                                   @Param("teacherId")Long teacherId,
                                                   @Param("status") Integer status,
                                                   @Param("createdAt") String createdAt
    );


    /**
     * 发放记录
     * @param yearPart
     * @param quarterNum
     * @param grantId
     * @param status
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.*,",
            "t2.NAME AS teacher_name ",
            "FROM",
            "book_grant_info_paper AS t1",
            "INNER JOIN book_student_paper AS t3 ON t1.book_student_id = t3.id and t3.deleted = 0",
            "INNER JOIN staff AS t2 ON t1.grant_id = t2.id ",
            "where",
            "t1.year_part = ${yearPart}",
            "and t1.quarter_num = ${quarterNum}",
            "<when test = 'grantId!=null'>",
            "and t1.grant_id = ${grantId}",
            "</when>",
            "and t1.status = ${status}",
            "AND t3.school_id = ${schoolId}",
            "AND t3.subjects_id = ${subjectsId}",
            "GROUP BY",
            "t1.grant_id,",
            "t1.created_at",
            "ORDER BY",
            "t1.created_at DESC",
            "<when test = 'limitCount!=null'>",
            "limit ${limitCount}",
            "</when>",
            "</script>"})
    public List<BookGrantInfo> getGrantPaperLog(@Param("yearPart") Integer yearPart,
                                                @Param("quarterNum") Integer quarterNum,
                                                @Param("schoolId") Long schoolId,
                                                @Param("subjectsId") Long subjectsId,
                                                @Param("grantId") Long grantId,
                                                @Param("status") Integer status,
                                                @Param("limitCount") Integer limitCount
    );
}
